package com.bringspring.system.base.service.impl;

import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.ds.GroupDataSource;
import com.bringspring.common.config.ConfigValueUtil;
import com.bringspring.common.database.constant.DbConst;
import com.bringspring.common.database.constant.ViewDataTypeConst;
import com.bringspring.common.database.data.DataSourceContextHolder;
import com.bringspring.common.database.enums.ParamEnum;
import com.bringspring.common.database.model.DataSourceModel;
import com.bringspring.common.database.model.DbTableDataForm;
import com.bringspring.common.database.model.DbTableFieldModel;
import com.bringspring.common.database.model.DbTableModel;
import com.bringspring.common.database.model.JdbcPageMod;
import com.bringspring.common.database.model.dto.DbConnDTO;
import com.bringspring.common.database.model.dto.JdbcDTO;
import com.bringspring.common.database.model.dto.PreparedStatementDTO;
import com.bringspring.common.database.model.interfaces.JdbcGetMod;
import com.bringspring.common.database.source.DbBase;
import com.bringspring.common.database.sql.SqlBase;
import com.bringspring.common.database.sql.impl.SqlKingbase;
import com.bringspring.common.database.util.*;
import com.bringspring.common.exception.DataException;
import com.bringspring.common.util.ParameterUtil;
import com.bringspring.common.util.StringUtils;
import com.bringspring.common.util.UserProvider;
import com.bringspring.system.base.model.dbtable.DbTableCreate;
import com.bringspring.system.base.model.dbtable.DbTableUpdate;
import com.bringspring.system.base.service.DbTableService;
import com.bringspring.system.base.service.DblinkService;
import lombok.Cleanup;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * 数据管理
 *
 * @author 开发平台组
 * @version V1.0.0
 * @copyright 股份有限公司
 * @date 2017年9月27日 上午9:18
 */
@Slf4j
@Service
public class DbTableServiceImpl implements DbTableService {

    @Autowired
    private DblinkService dblinkService;
    @Autowired
    private DataSourceModel dataSourceUtils;
    @Autowired
    private UserProvider userProvider;
    @Autowired
    private ConfigValueUtil configValueUtil;
    @Autowired
    DbTableService dbTableService;
    @Autowired
    private DataSource dataSource;

    @Override
    public List<DbTableModel> getList(String dbId) throws DataException {
        DbConnDTO connDTO = setResource(dbId);
        List<DbTableModel> tableModelList = getListCommon(null, DbTableModel.class, connDTO);
        return tableModelList;
    }

    @Override
    public List<DbTableFieldModel> getFieldList(String dbId, String table) throws DataException {
        DbConnDTO connDTO = setResource(dbId);
        List<DbTableFieldModel> listCommon = getListCommon(table, DbTableFieldModel.class, connDTO);
        return listCommon;
    }



    /**=======================jdbc查询========================**/

    public <T extends JdbcGetMod>List<T> getListCommon(String table, Class<T> modType, DbConnDTO connDTO) throws DataException {
        try {
            @Cleanup Connection conn = connDTO.getConn();
            //获取查询sql语句
            SqlBase sqlBase = connDTO.getDbBase().getSqlBase();
            PreparedStatementDTO preparedStatementDTO = null;
            if(modType == DbTableModel.class){
                preparedStatementDTO = sqlBase.getTableListPSD(conn, connDTO.getDbSource());
            }else {
                preparedStatementDTO = sqlBase.getFieldListPSD(conn, table, connDTO.getDbSource());
            }
            //根据sql语句获取List
            return JdbcUtil.queryCustomMods(preparedStatementDTO, modType);
        }catch (Exception e){
            e.printStackTrace();
            throw  new DataException(e.getMessage());
        }
    }


    @Override
    public boolean isExistByTableName(String dbId, String table) throws DataException {
        try {
            DbConnDTO connDTO = setResource(dbId);
            @Cleanup Connection conn = connDTO.getConn();
            //获取查询sql语句
            SqlBase sqlBase = connDTO.getDbBase().getSqlBase();
            PreparedStatementDTO preparedStatementDTO = sqlBase.getExistsTablePSD(conn, table, connDTO.getDbSource());
            //根据sql语句获取List
            List<Map<String, Object>> list = JdbcUtil.queryList(preparedStatementDTO);
            return list != null && !list.isEmpty();
        }catch (Exception e){
            throw  new DataException(e.getMessage());
        }
    }

    @Override
    public List<Map<String, Object>> getData(DbTableDataForm dbTableDataForm, String dbId, String table) throws Exception {
        List<Map<String, Object>> list = null;
        JdbcPageMod result = new JdbcPageMod();
        DbConnDTO connDTO = setResource(dbId);
        @Cleanup Connection conn = connDTO.getConn();
        if (conn != null) {
            LinkedList<Object> dataList = new LinkedList<>();
            StringBuilder sql = new StringBuilder(connDTO.getDbBase().getSqlBase().getDataSql(table));
            //模糊查询
            if (!StringUtils.isEmpty(dbTableDataForm.getKeyword()) && !StringUtils.isEmpty(dbTableDataForm.getField())) {
                sql.append(" where " + dbTableDataForm.getField() + " like ?");
                dataList.add("%" + dbTableDataForm.getKeyword() + "%");
            }
            JdbcDTO<JdbcGetMod> jdbcDTO = new JdbcDTO<>();
            jdbcDTO.setReturnType(DbConst.MAP_MOD);
            jdbcDTO.setAliasFlag(true);
            jdbcDTO.setLowercaseFlag(true);
            result = JdbcUtil.queryPage(new PreparedStatementDTO(conn, sql.toString(), dataList), null, (int) dbTableDataForm.getCurrentPage(), (int) dbTableDataForm.getPageSize(), jdbcDTO);
        }
        dbTableDataForm.setCurrentPage(result.getCurrentPage());
        dbTableDataForm.setPageSize(result.getPageSize());
        dbTableDataForm.setData(result.getDataList(), result.getTotalRecord());
        return result.getDataList();
    }

    @Override
    public int getSum(String dbId,String table)throws DataException {
        int sum = 0;
        try {
            DbConnDTO connDTO = setResource(dbId);
            @Cleanup Connection conn = connDTO.getConn();
            if (conn != null) {
                String sql = SqlKingbase.DATA_SUM_SQL.replace(ParamEnum.TABLE.getTarget(),table);
                PreparedStatementDTO dto = new PreparedStatementDTO(conn, sql);
                sum = JdbcUtil.queryOneInt(dto, "COUNT_SUM");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sum;
    }

    /**=====================增删改========================**/

    @Override
    public void delete(String dbId, String table) throws DataException {
        if (!sumExistFlag(dbId, table)) {
            if (checkRepetition(table)) {
                throw new DataException("系统自带表,不允许被修改");
            }
        }else {
            throw new DataException("表已经被使用,不允许被编辑");
        }
        try {
            DbConnDTO connDTO = setResource(dbId);
            @Cleanup Connection conn = connDTO.getConn();
            JdbcUtil.creUpDe(connDTO.getDbBase().getSqlBase().getDeleteSqlPSD(conn, table));
        }catch (Exception e){
            throw new DataException(e.getMessage());
        }
    }

    @Override
    public int createTable(DbTableCreate dbTableCreate) throws DataException {
        String dbLink = dbTableCreate.getDbLinkId();
        DbConnDTO connDTO = setResource(dbLink);
        SqlBase sqlBase = connDTO.getDbBase().getSqlBase();
        try{
            @Cleanup Connection conn = connDTO.getConn();
            try{
                //表重名判断
                if (dbTableService.isExistByTableName(dbLink, dbTableCreate.getNewTable())) {
                    return 0;
                }
                //创建表
                checkPrimary(dbTableCreate.getDbTableFieldModelList());
                DbBase dbBase = DbTypeUtil.getDb(conn);
                JdbcUtil.creUpDeBatch(conn,
                        sqlBase.getCreateTablePSD(
                                dbTableCreate.getNewTable(),
                                dbTableCreate.getTableComment(),
                                dbTableCreate.getDbTableFieldModelList(),dbBase));
            }catch (Exception e){
                //创建的语句无法回滚，手动删除删除
                JdbcUtil.creUpDe(sqlBase.getDeleteSqlPSD(conn, dbTableCreate.getNewTable()));
                throw new DataException(e.getMessage());
            }
            return 1;
        }catch (Exception e){
            throw new DataException(e.getMessage());
        }
    }


    @Override
    public void update(DbTableUpdate dbTableUpdate) throws DataException {
        if (!sumExistFlag(dbTableUpdate.getDbLinkId(), dbTableUpdate.getOldTable())) {
            if (checkRepetition(dbTableUpdate.getNewTable())) {
                throw new DataException("系统自带表,不允许被修改");
            }
        }else {
            throw new DataException("表已经被使用,不允许被编辑");
        }
        try{
            DbConnDTO connDTO = setResource(dbTableUpdate.getDbLinkId());
            //临时表名
            String oldTable = dbTableUpdate.getOldTable();
            String signTableName = oldTable + "_" + CommonUtil.getStringRandom(5);
            SqlBase sqlBase = connDTO.getDbBase().getSqlBase();
            @Cleanup Connection conn = connDTO.getConn();
            try{
                // 第一步：旧表改名（临时表名）
                // 表名无法用?占位符的方式
                String sql = sqlBase.getRenameSql(oldTable, signTableName);
                JdbcUtil.creUpDe(new PreparedStatementDTO(conn, sql));
                DbBase dbBase = DbTypeUtil.getDb(conn);
                //第二步：创建新表 (成功则删除旧表，失败则回滚)
                JdbcUtil.creUpDeBatch(conn,
                        sqlBase.getCreateTablePSD(
                                dbTableUpdate.getNewTable(),
                                dbTableUpdate.getTableComment(),
                                dbTableUpdate.getDbTableFieldModelList(),dbBase));
            }catch (Exception e){
                //旧表改回原名(回滚conn.rollback()无法滚回ALTER语句等DDL操作语句)
                JdbcUtil.creUpDe(new PreparedStatementDTO(conn, sqlBase.getRenameSql(signTableName, oldTable)));
                throw e;
            }
            //手动提交
            conn.commit();
            //第三步：删除旧表
            JdbcUtil.creUpDe(sqlBase.getDeleteSqlPSD(conn, signTableName));
        }catch (Exception e){
            throw new DataException(e.getMessage());
        }
    }

    @Override
    public void getSyncTable(String lindId) {
        try{
            @Cleanup Connection conn = ConnUtil.ConnCommon.getConnRemarks(dblinkService.getInfo(lindId));
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
    }

    /**================复用代码==================**/

    /**
     * 检查主键
     * @param tableFieldList
     * @throws DataException
     */
    private void checkPrimary(List<DbTableFieldModel> tableFieldList) throws DataException {
        //默认主键为字符串类型
        //主键会自动添加"非空"限制，所以不用做判断。(为空不加语句，且数据库默认字段可为空)
        for(DbTableFieldModel field : tableFieldList) {
            if (field.getPrimaryKey() == 1) {
                if(!field.getDataType().equals(ViewDataTypeConst.VARCHAR)){
                    throw new DataException("请修改主键为默认\"字符串\"类型。");
                }
            }
        }
    }

    /**
     * 设置数据源
     * @param linkId 数据连接id
     * @throws DataException ignore
     */
    private DbConnDTO setResource(String linkId) throws DataException {
        DataSourceModel dbSourceOrDbLink;
        try {
            if("0".equals(linkId)){
                //多租户是否开启
                String tenSource = Boolean.parseBoolean(configValueUtil.getMultiTenancy()) ?
                        userProvider.get().getTenantDbConnectionString() : null;
                //默认数据库查询，从配置获取数据源信息
                dbSourceOrDbLink = dataSourceUtils;
                // 是系统默认的多租户
                if (!"true".equals(tenSource)) {
                    if(tenSource!=null){
                        dbSourceOrDbLink.setDbName(tenSource);
                    }
                } else {
                    DynamicRoutingDataSource dynamicRoutingDataSource = (DynamicRoutingDataSource) dataSource;
                    if (dynamicRoutingDataSource.getGroupDataSources().containsKey(DataSourceContextHolder.getDatasourceId() + "-master")) {
                        GroupDataSource groupDataSource = dynamicRoutingDataSource.getGroupDataSources().get(DataSourceContextHolder.getDatasourceId() + "-master");
                        if (groupDataSource.getDataSourceMap().size() > 0) {
                            String key = "";
                            for (String string : groupDataSource.getDataSourceMap().keySet()) {
                                key = string;
                                break;
                            }
                            DruidDataSource dataSource = (DruidDataSource)groupDataSource.getDataSourceMap().get(key);
                            String url = dataSource.getUrl();
                            String username = dataSource.getUsername();
                            String password = dataSource.getPassword();
                            String driverClassName = dataSource.getDriverClassName();
                            dbSourceOrDbLink.setUserName(username);
                            dbSourceOrDbLink.setPassword(password);
                            dbSourceOrDbLink.setPrepareUrl(url);
                            dbSourceOrDbLink.setDbType(ParameterUtil.getDbType(driverClassName));
                        }
                    }
                }
            }else {
                dbSourceOrDbLink = dblinkService.getInfo(linkId);
            }
            return new DbConnDTO(DbTypeUtil.getDb(dbSourceOrDbLink),
                    dbSourceOrDbLink, ConnUtil.getConn(dbSourceOrDbLink));
        } catch (Exception e){
            throw new DataException(e.getMessage());
        }
    }

    /**
     * 存在数据不可修改
     *
     * @param linkId   连接id
     * @param oldTable ignore
     * @return ignore
     * @throws DataException ignore
     */
    private Boolean sumExistFlag(String linkId, String oldTable) throws DataException {
        ///
        //默认数据库判断
        /*if("0".equals(linkId)){
            boolean existLink = false;
            String dsDbUsName = dataSourceUtils.getUserName();
            String dsDbPassword = dataSourceUtils.getPassword();
            String dsDbName = dataSourceUtils.getDbName();
            String dsDbType = dataSourceUtils.getDataType();
            String dsDbHost = dataSourceUtils.getHost();
            String dsDbPort = dataSourceUtils.getPort();
            //获取所有设置过的连接
            List<DbLinkEntity> linkList = dblinkService.getList();
            for(DbLinkEntity dbLinkEntity:linkList){
                if (DbTypeUtil.compare(dsDbType,dbLinkEntity.getDbType()) &&
                        dsDbUsName.equals(dbLinkEntity.getUserName())&&
                        dsDbPassword.equals(dbLinkEntity.getPassword()) &&
                        dsDbName.equals(dbLinkEntity.getServiceName())&&
                        dsDbHost.equals(dbLinkEntity.getHost()) &&
                        dsDbPort.equals(dbLinkEntity.getPort())
                ){
                    existLink = true;
                    linkId = dbLinkEntity.getId();
                }
            }
            //未找到相对应的连接
            if(!existLink){throw DataException.configDbLoseLink();}
        }*/
        ///
        return checkCommon(linkId, oldTable);
    }

    /**
     * 获取表数据行数
     * 说明：检查表格是否存在数据（存在不可删除）
     *
     * @param linkId    连接id
     * @param tableName 表名
     * @return Integer
     * @throws DataException ignore
     */
    private Boolean checkCommon(String linkId, String tableName) throws DataException {
        List<DbTableModel> tableList = dbTableService.getList(linkId);
        //筛选出指定表名的表
        Optional<DbTableModel> table = tableList.stream().filter(m -> m.getTable().equals(tableName)).findFirst();
        //表存在判断
        if (table.isPresent()) {
            Integer sum = table.get().getSum();
            if (sum != null) {
                log.debug("表的数据总数sum是：" + sum.toString());

            } else {
                sum = dbTableService.getSum(linkId, tableName);
                /*throw new DataException("数据库表总数出现异常，导致无法删除。");*/
            }
            if (sum != 0 && sum > 0) {
                return true;
            } else {
                log.debug("表的数据表信息：" + table.get().toString());
                return false;
            }
        } else {
            throw new DataException("该表已被删除，或者不存在，请刷新页面后再尝试。");
        }
    }

    /**
     * 复用代码
     *
     * @param tableName 表明
     * @return ignore
     */
    private Boolean checkRepetition(String tableName) {
        String[] tables = DbConst.BYO_TABLE.split(",");
        boolean exists;
        for (String table : tables) {
            exists = tableName.toLowerCase().equals(table);
            if (exists) {
                return true;
            }
        }
        return false;
    }

}
